package dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.Room;
import model.RoomHasBooking;
import util.DBController;
import dao.intf.RoomDao;

public class RoomDaoImpl implements RoomDao {

	public List<Room> getRoomList() {
		List<Room> roomList = new ArrayList<Room>();
		
		ResultSet rs = DBController.retrieve("Room", null, null);
		
		try {
			if( rs != null ) {
				while( rs.next() ) {
					Room room = new Room();
					room.setRoomId(rs.getString("RoomID"));
					room.setTypeId(rs.getString("TypeID"));
					room.setStatus(rs.getString("Status"));
					
					roomList.add(room);
				}
			}
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at method getRoomList() of RoomDaoImpl.");
			e.printStackTrace();
		}
		
		return roomList;
	}

	public Room getRoomById(String id) {
		Room room = new Room();
		
		ResultSet rs = DBController.retrieve("Room", null, "RoomID='"+id+"'");
		
		try {
			if( rs != null ) {
				while( rs.next() ) {
					room.setRoomId(rs.getString("RoomID"));
					room.setTypeId(rs.getString("TypeID"));
					room.setStatus(rs.getString("Status"));
				}
			}
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at method getRoomById() of RoomDaoImpl.");
			e.printStackTrace();
		}
		
		return room;
	}
	
	public List<Room> getAvailableRooms(String start, String end, String roomType) {
		List<Room> roomList = new ArrayList<Room>();
		
		String condition = "RoomID not in (Select RoomID from Booking, Room_has_Booking " +
							"where DATEDIFF(Start,'"+start+"') <= 0 " +
							"and DATEDIFF(end, '"+end+"') >= 0 " +
							"and Booking.ID = Room_has_Booking.BookingID) " +
							"and TypeID='"+roomType+"'";
		
		ResultSet rs = DBController.retrieve("Room", null, condition);
		
		try {
			if( rs != null ) {
				while( rs.next() ) {
					Room room = new Room();
					room.setRoomId(rs.getString("RoomID"));
					room.setTypeId(rs.getString("TypeID"));
					room.setStatus(rs.getString("Status"));
					
					roomList.add(room);
				}
			}
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at method getRoomList() of RoomDaoImpl.");
			e.printStackTrace();
		}
		
		return roomList;
	}
	
	public int updateRoomStatus(String roomList,String status) {
		int recordCount=0;
		try {
			if(roomList!=null) {
				String queryStr	 = " UPDATE Room ";
						queryStr+= " SET Status='"+status+"'";
						queryStr+= " WHERE RoomID in ( "+roomList+")";
					
				
				recordCount=DBController.save(queryStr);
				}
			
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at saveBooking BookingDaoImpl.");
			e.printStackTrace();
		}
		
		return recordCount;
	}

	
	public  List<Room>  getRoom(String typeId,String status) {
		List<Room> roomList = new ArrayList<Room>();
		String condition="";
		if(status!=""){
			 condition= "  status='"+status+"'";
			   condition+= " AND typeId='"+typeId.trim()+"'";
		}else{  condition+= "  typeId='"+typeId.trim()+"'";}

			ResultSet rs = DBController.retrieve("ROOM", null, condition);
			
			try {
				if( rs != null ) {
					while( rs.next() ) {
						Room room = new Room();
						room.setRoomId(rs.getString("RoomID"));
						room.setTypeId(rs.getString("TypeID"));
						room.setStatus(rs.getString("Status"));
						
						roomList.add(room);
					}
				}
				DBController.disconnect();
			} catch (Exception e) {
				System.out.println("Error at method getRoomList() of RoomDaoImpl.");
				e.printStackTrace();
			}
			
			return roomList;
	}
	public List<Room> getExpiredRoom()
	{
		List<Room> roomList = new ArrayList<Room>();
		
			
		String queryStr =" SELECT * FROM "; 
			   queryStr+=" ROOm ,ROOM_HAS_BOOKING, bOOKING ";
			   queryStr+=" where (DATE(BOOKING.end) <= DATE(NOW()) OR Booking.BookingStatus='C')";
			   queryStr+=" and ROOM.roomid=ROOM_HAS_BOOKING.roomid";
			   queryStr+=" and room_has_booking.bookingid=booking.id";
			   queryStr+=" and room.Status='R'";
			   queryStr+=" and room.roomid not in (select room_has_booking.roomid from room_has_booking,booking where booking.end>=Date(now()) and room_has_booking.bookingid=booking.id)";

			ResultSet rs = DBController.retrieve(queryStr);
			
			try {
			if( rs != null ) {
				while( rs.next() ) {
					Room room = new Room();
					room.setRoomId(rs.getString("RoomID"));
					room.setTypeId(rs.getString("TypeID"));
					room.setStatus(rs.getString("Status"));
					
					roomList.add(room);
				}
			}
			DBController.disconnect();
			} catch (Exception e) {
			System.out.println("Error at method getRoomList() of RoomDaoImpl.");
			e.printStackTrace();
			}
			
			return roomList;
					
	}
	
	

}
